#!/usr/bin/env python
# encoding: utf-8
# @author: liusir
# @file: demo_02.py
# @time: 2022/11/2 9:08 PM

import xlrd3

work_book = xlrd3.open_workbook( 'test_data.xlsx' ) # 加载表格文件
sheet = work_book.sheet_by_name('Sheet1') # 根据表格名称创建表格对象
col_num = sheet.ncols # 获取列总数
row_num = sheet.nrows # 获取行总数
print( row_num )
print( sheet.row(0) ) # 获取一行的数据
print( sheet.cell_value(2,2) )  # 根据下标得出单元格的值
# **********************************
print( sheet.cell_value(1,0) )
print( sheet.cell_value(2,0) )
print( sheet.cell_value(3,0) )
print( sheet.cell_value(4,0) )
print( sheet.merged_cells ) #获取当前文档中的所有合并单元格的位置信息
#它返回的是一个列表，每一个元素是合并单元格的位置信息的数组，
# 数组包含四个元素（起始行，结束行，起始列，结束列）
# 判断单元格是否是 合并的单元格，如果是合并单元格，让这个单元格的值取合并单元格第一个的值
# [(1, 5, 0, 1), (5, 7, 0, 1)]
x=3;y=0
if x>=1 and x<5:
    if y>=0 and y<1:
        print( '合并单元格' )
    else:
        print( '不是合并单元格' )
else:
    print( '不是合并单元格' )

x=6;y=0
# for (min_row,max_row,min_col,max_col) in [(1, 5, 0, 1), (5, 7, 0, 1)]:
#     if x >= min_row and x < max_row:
#         if y >= min_col and y < max_col:
#             print('合并单元格')
#             break
#         else:
#             print('不是合并单元格')
#     else:
#         print('不是合并单元格')
# 多个单元格合并，所有被合并单元格的值 都 等于 第一个合并单元格的值
# for (min_row,max_row,min_col,max_col) in sheet.merged_cells:
#     if x >= min_row and x < max_row:
#         if y >= min_col and y < max_col:
#             cell_value = sheet.cell_value(min_row,min_col)
#             break
#         else:
#             cell_value = sheet.cell_value(x,y)
#     else:
#         cell_value = sheet.cell_value(x,y)
# print( cell_value )

# 转换成方法
def get_cell_merged_value(row_index,col_index):
    for (min_row, max_row, min_col, max_col) in sheet.merged_cells:
        if row_index >= min_row and row_index < max_row:
            if col_index >= min_col and col_index < max_col:
                cell_value = sheet.cell_value(min_row, min_col)
                break
            else:
                cell_value = sheet.cell_value(row_index, col_index)
        else:
            cell_value = sheet.cell_value(row_index, col_index)
    return cell_value

print( get_cell_merged_value(3,2) )
# 遍历获取excel的所有数据
# for i in range(0,7):
#     for j in range(0,4):
#         cell_value = get_cell_merged_value(i,j)
#         print( cell_value ,end=" " )
#     print()

excel_list_data = []
row_one = sheet.row_values(0) # 获取指定行所有的内容
row_dict = {}
row_dict[ row_one[0] ] = get_cell_merged_value(1,0)
row_dict[ row_one[1] ] = get_cell_merged_value(1,1)
row_dict[ row_one[2] ] = get_cell_merged_value(1,2)
row_dict[ row_one[3] ] = get_cell_merged_value(1,3)
print( row_dict )

# 换成循环处理该数据 , 一行的数据格式正确
excel_list_data = []
row_one = sheet.row_values(0) # 获取指定行所有的内容
row_dict = {}
for i in range(sheet.ncols):
    row_dict[ row_one[i] ] = get_cell_merged_value(1,i)
print( row_dict )

# 获取多行数据
excel_list_data = []
row_one = sheet.row_values(0) # 获取指定行所有的内容
for i in range(1,sheet.nrows):
    row_dict = {}
    for j in range(sheet.ncols):
        row_dict[ row_one[j] ] = get_cell_merged_value(i,j)
    excel_list_data.append( row_dict )

for data in excel_list_data:
    print( data )
